package com.yikome.report.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.yikome.report.request.OrderReportRequest;
import com.yikome.report.response.OrderReportResponse;

import cn.hutool.core.util.StrUtil;

@Service
public class OrderReportService {

	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate ; 
	
	
	public List<OrderReportResponse> findOrderReports(OrderReportRequest orderReportRequest){
		
		StringBuilder paramSQL = new StringBuilder("");
		Map<String , Object> paramsMap = new HashMap<>();
		
		if(StrUtil.isNotBlank(orderReportRequest.getAffiliate())) {
			paramSQL.append("	AND o_order.affiliate = :affiliate \n");
			paramsMap.put("affiliate", orderReportRequest.getAffiliate().trim());
		}
		if(StrUtil.isNotBlank(orderReportRequest.getCurrency())) {
			paramSQL.append("	AND o_order.currency = :currency \n");
			paramsMap.put("currency", orderReportRequest.getCurrency().trim());
		}
		if(orderReportRequest.getBegainTime() != null && orderReportRequest.getEndTime() != null) {
			paramSQL.append("	AND o_order.created_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
			paramsMap.put("begainTime", orderReportRequest.getBegainTime());
			paramsMap.put("endTime", orderReportRequest.getEndTime());
		}
		
		String contentSQL = "SELECT \n" + 
							"	vo.affiliate, vo.currency, COUNT(vo.`code`) order_count, SUM(vo.price) order_price,  \n" + 
							"	SUM(vo.reference_purchase_prices) order_purchase_prices, SUM(vo.order_product_quantites) order_product_quantites, SUM(vo.order_product_lists) order_product_lists \n" + 
							"FROM ( \n" + 
							"	SELECT \n" + 
							"		o_order.affiliate, o_order.`code`, o_order.price, o_order.freight, o_order.currency,  \n" + 
							"		SUM(o_order_product.reference_purchase_price) reference_purchase_prices, \n" + 
							"		SUM(o_order_product.quantity) order_product_quantites,  \n" + 
							"		COUNT(o_order_product.product_sku_barcode) order_product_lists \n" + 
							"	FROM o_order \n" + 
							"		LEFT JOIN o_order_product ON (o_order.`code` = o_order_product.order_code) \n" + 
							"	WHERE o_order.yn = 1 \n" + paramSQL + 
							"	GROUP BY o_order.`code` \n" + 
							") vo \n" + 
							"GROUP BY vo.affiliate, vo.currency \n" ; 
		
		return jdbcTemplate.query(contentSQL, paramsMap, new BeanPropertyRowMapper<>(OrderReportResponse.class));
	}
}
